package com.tangdi.mkfile.gzdwhz;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import com.tangdi.ReportFileInfo;
import com.tangdi.Utils;
import com.tangdi.Bean.CZGZ_DWXX;
import com.tangdi.Bean.CZGZ_DWXX_TOTAL;
import com.tangdi.Dao.impl.GongziDWImpl;
import com.tangdi.Dao.impl.GongziMXImpl;

public class CreateDWGZExcel_LISTALL {
	//private Logger logger = Logger.getLogger(CreateDWGZExcel_LISTALL.class);
	private String excel_url_root = Utils.getFilePath("root_path");
	private String folder_Name = ReportFileInfo.FOLDER_NAME_GZDWHZ.getName();
	private String file_Type = "excel";
	private String file_Name = ReportFileInfo.FILE_NAME_GZDWHZ.getName();
	private int initSize = 8;
	private String title_content = "月单位工资发放汇总表";
	public int createExcel(List<CZGZ_DWXX> listDW, CZGZ_DWXX_TOTAL dwCount, String month) {		
	 try {
	 	String excel_url = excel_url_root + "/" + month + "/" + file_Type + "/" + folder_Name + "/" + month + "_" + file_Name + ".xls";
	 	//在填写文件路径时，一定要写上具体的文件名称（xx.txt），否则会出现拒绝访问。
			File file = new File(excel_url);
			if(!file.exists()){
			 //先得到文件的上级目录，并创建上级目录，在创建文件
				file.getParentFile().mkdirs();
				file.createNewFile();
			}
			HSSFWorkbook wb = new HSSFWorkbook();
			//字体
			Font headerFont = getFont(wb, "微软雅黑", 18);
			Font contextFont = getFont(wb, "微软雅黑", 12);
			//样式 
			CellStyle headerStyle = getCellStyle(wb, "noboder", CellStyle.ALIGN_CENTER, headerFont);
			CellStyle contentStyle = getCellStyle(wb, "noboder", CellStyle.ALIGN_LEFT, contextFont);
			CellStyle nameStyle = getCellStyle(wb, "border", CellStyle.ALIGN_CENTER, contextFont);
			CellStyle valueStyle_left = getCellStyle(wb, "border", CellStyle.ALIGN_LEFT, contextFont);
			CellStyle valueStyle_right = getCellStyle(wb, "border", CellStyle.ALIGN_RIGHT, contextFont);
			
			Sheet sheet = wb.createSheet("sheet1");
			sheet.setColumnWidth(0, 40*256);
			sheet.setColumnWidth(1, 28*256);
			sheet.setColumnWidth(2, 28*256);
			sheet.setColumnWidth(3, 12*256);
			for(int a = 4;a < initSize - 1;a++){
				sheet.setColumnWidth(a, 18*256);
		 }
			sheet.setColumnWidth(initSize - 1, 15*256);
			
			//第 1 行 标题
			Row row0 = sheet.createRow(0);
			addCell_Value(row0, 0, dwCount.getYF().substring(0, 4) + "年" + dwCount.getYF().substring(4, 6) + title_content, headerStyle);
			//第 2 行 单位数量
			Row row1 = sheet.createRow(2);
			addCell_Value(row1, 0, "单位总数："+ dwCount.getDWCOUNT().trim(), contentStyle);			
			//第 3 行 列名 
			Row row2 = sheet.createRow(3);
			addCell_Value(row2, 0, "单位名称", nameStyle);
			addCell_Value(row2, 1, "代扣款银行", nameStyle);
			addCell_Value(row2, 2, "代扣款银行账号", nameStyle);
			addCell_Value(row2, 3, "人数", nameStyle);
			addCell_Value(row2, 4, "应发", nameStyle);
			addCell_Value(row2, 5, "扣款", nameStyle);
			addCell_Value(row2, 6, "实发", nameStyle);
			addCell_Value(row2, 7, "个人所得税", nameStyle);
			//第 4 行 汇总 合计
			GongziMXImpl gzmx = new GongziMXImpl();
			String geShui_Count = "0";
			
			Row row3 = sheet.createRow(4);
			addCell_Value(row3, 0, "合    计", valueStyle_left);
			addCell_Value(row3, 1, "", valueStyle_left);
			addCell_Value(row3, 2, "", valueStyle_left);
			addCell_Value(row3, 3, dwCount.getTF_RS_TOTAL(), valueStyle_right);
			addCell_Value(row3, 4, dwCount.getYF_GZ_TOTAL(), valueStyle_right);
			addCell_Value(row3, 5, dwCount.getDF_GZ_TOTAL(), valueStyle_right);
			addCell_Value(row3, 6, dwCount.getSF_GZ_TOTAL(), valueStyle_right);
			
			geShui_Count = gzmx.getDWRSCount_ByLB(month, "SELECT COALESCE(FORMAT(TO_NUMBER(SUM(B009)),2),'0') AS B009_SUM FROM CZGZ_ZGGZMX WHERE YF = '" + month +"'" );
			geShui_Count = geShui_Count.trim();
			if(geShui_Count != null){
				addCell_Value(row3, 7, (geShui_Count.equals("0.00") || geShui_Count.equals("0")) ? "" : geShui_Count, valueStyle_right);
			}else{
				addCell_Value(row3, 7, " ", valueStyle_right);
			}
						
			for(int i=0;i<listDW.size();i++){
				CZGZ_DWXX dw = listDW.get(i);
				Row rowTemp = sheet.createRow(i + 5);
				addCell_Value(rowTemp, 0, dw.getDW_MC(), valueStyle_left);
				addCell_Value(rowTemp, 1, "交通银行玄武支行", valueStyle_left);
				addCell_Value(rowTemp, 2, "320006610018010133201", valueStyle_left);
				addCell_Value(rowTemp, 3, dw.getTF_RS(), valueStyle_right);
				addCell_Value(rowTemp, 4, dw.getYF_GZ(), valueStyle_right);
				addCell_Value(rowTemp, 5, dw.getDF_GZ(), valueStyle_right);
				addCell_Value(rowTemp, 6, dw.getSF_GZ(), valueStyle_right);
				
				geShui_Count = "0";
				geShui_Count = gzmx.getDWRSCount_ByLB(month, "SELECT COALESCE(FORMAT(TO_NUMBER(SUM(B009)),2),'0') AS B009_SUM FROM CZGZ_ZGGZMX WHERE YF = '" + month +"' AND DW_DM = '" + dw.getDW_DM() + "'" );
				geShui_Count = geShui_Count.trim();
				if(geShui_Count != null){
					addCell_Value(rowTemp, 7, (geShui_Count.equals("0.00") || geShui_Count.equals("0")) ? "" : geShui_Count, valueStyle_right);
				}else{
					addCell_Value(rowTemp, 7, " ", valueStyle_right);
				}
				
			}			
			//
			Row row_last = sheet.createRow(listDW.size() + 6);
			addCell_Value(row_last, 0, "银行付讫（盖章）：", contentStyle);
			addCell_Value(row_last, 2, "制表人：", contentStyle);
			addCell_Value(row_last, 5, "制表日期：", contentStyle);
			
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
			
			FileOutputStream os = new FileOutputStream(excel_url);
			wb.write(os);
			os.close();
			
	 } catch (Exception e) {
	 	e.printStackTrace();
	 	return -1;
		}
	 return 0;
	}
	public static CellStyle getCellStyle(HSSFWorkbook wb, String borderType, short align, Font font){
		CellStyle style = wb.createCellStyle();
		style.setAlignment(align);
		if(borderType.equals("border")){
			style.setBorderTop(CellStyle.BORDER_THIN);
			style.setBorderBottom(CellStyle.BORDER_THIN);
			style.setBorderLeft(CellStyle.BORDER_THIN);
			style.setBorderRight(CellStyle.BORDER_THIN);
		}
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
		style.setLocked(true);
		style.setWrapText(true);
		style.setFont(font);
		return style;
	}
	public static Font getFont(HSSFWorkbook wb, String fontName, int fontSize){
		Font font = wb.createFont();
		font.setFontName(fontName);
		font.setFontHeightInPoints((short)fontSize);
		font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		return font;
	}
	public static void addCell_Value(Row row, int a, String value, CellStyle cellStyle){
		Cell cell = row.createCell(a);
		cell.setCellValue(value);
		cell.setCellStyle(cellStyle);
	}
	public static void main(String[] args) {
		//年月
		String month = "201905";
		//单位工资信息list
		List<CZGZ_DWXX> listDW=new ArrayList<CZGZ_DWXX>();
		GongziDWImpl gzDW = new GongziDWImpl();
		listDW = gzDW.listGZDWReport(month);
		
		//单位工资汇总
		CZGZ_DWXX_TOTAL dwCount=new CZGZ_DWXX_TOTAL();
		GongziDWImpl gzDWcount = new GongziDWImpl();
		dwCount = gzDWcount.getGZDWCount(month);
		
		System.out.println("listDW:" + listDW.size());
		
		CreateDWGZExcel_LISTALL c = new CreateDWGZExcel_LISTALL();
	 c.createExcel(listDW, dwCount, month);
	 System.out.println("制作完成");
	}
}
